import numpy as np
import xlrd
import pandas as pd
import matplotlib.pyplot as plt
import xlwt
from PIL import Image
import math


def get_data(path, sheel_name):
    '''
    将数据输出为Dataframe
    :param path: 获取数据所需文件的路径
    :param sheel_name: 文件对应的表格名
    :return:
    '''
    df = pd.read_excel(path, sheet_name=sheel_name, header=6)
    lesson_number = int((len(df.columns) - 14) / 3)
    column1 = ['学号', '姓名', '课程编号', '考试科目', '考试状态', '性别', '留学生', '学院', '考试日期', '班级', '年纪', '备注', '平时成绩', '考试成绩', '总成绩']
    column2 = []
    column3 = []
    for i in range(1, lesson_number + 1):
        column2.append('平时成绩课程目标' + str(i))
        column2.append('考试成绩课程目标' + str(i))
        column2.append('课程目标总成绩' + str(i))
        column3.append('课程目标总成绩' + str(i))
    column3.append('总目标达成度')
    column2.append('总目标达成度')
    column = column1 + column2
    df.columns = column
    data = df.fillna(0)
    return data, column3


def screen_data(data, *leibie_and_name):
    '''
    筛选数据
    :param data: 数据
    :param leibie_and_name: 筛选条件，例如 ['学号', ['2017', '2019']],['姓名',['XXX']] 筛选学号为 2017和2019，姓名为XXX的数据。leibie列表 ['学号', '姓名', '课程编号', '考试科目', '考试状态', '性别', '留学生', '学院', '考试日期', '班级', '年纪', '备注', '平时成绩', '考试成绩', '总成绩','平时成绩课程目标','考试成绩课程目标'，’课程目标总成绩‘，'总目标达成度']
    :return:
    '''
    list_leibie = []
    for x in leibie_and_name:
        list_leibie.append(x)
    number = len(list_leibie)
    for a in range(number):
        data = data.query(f'{list_leibie[a][0]} == {list_leibie[a][1]}')
    return data


# def getNumberList(alist):
#     '''
#     防止获取分数或权重列表时，出现空缺等非数字字符串导致运算错误
#     :param alist: 列表（某行或者某列数据）
#     :return: 将非数字格式数据转换以0代替
#     '''
#     for i in range(len(alist)):
#         if (type(alist[i]) != int) & (type(alist[i]) != float):
#             alist[i] = 0
#     return alist
#
# def getData(sheet,student_count,columns,start_row=7):
#     '''
#     为了画图方便，将有效数据(从第一个学生开始的数据)转成Dataframe格式，注意他们的列索引
#     :param sheet:
#     :param student_count:
#     :param start_row:
#     :return:
#     '''
#     end_row=start_row+student_count-1
#     data=[]
#     for i in range(start_row,end_row+1):
#         data.append(sheet.row_values(i))
#     data=pd.DataFrame(data)
#     data.columns=columns
#     return data
#
# def selectData(raw_data,colleges,including_nigger=True):
#     '''
#     colleges=='all'直接返回所有学院学生
#     :param raw_data:获取的DataFrame类型数据
#     :param colleges: 想要统计的学院名称，list /  str：’all‘
#     :param including_nigger: 是否包含留学生，bool
#     :return: 用于统计分析的数据，Dataframe
#     '''
#     # 是否筛选留学生
#     if  including_nigger==False:
#         data=raw_data.loc[raw_data['留学生']==0]
#     else:
#         data=raw_data
#     #留取选中的学院学生
#     if colleges=='all':
#         return data
#     else:
#         data=data.loc[data['学院'].isin(colleges)]
#         return data

def caculateData(col_data):
    df = pd.DataFrame()
    df['score_label'] = pd.cut(x=col_data, bins=[0, 0.5999, 0.6999, 0.7999, 0.8999, 1], labels=[1, 2, 3, 4, 5])
    return df.value_counts().sort_index()


def getDescribeInfo(select_data, completion_list):
    # 数据选择
    describe_info = []
    for i in completion_list:
        col_data = select_data[i].tolist()
        temp = caculateData(col_data).tolist()
        col_data = np.array(col_data)[np.array(col_data) > 0]
        temp.append(col_data.mean())
        temp.append(min(col_data))
        temp.append(max(col_data))
        describe_info.append(temp)
    return describe_info


def drawDescibe(describe_info, target_list, rank_list):
    # 饼状图绘制
    data = []
    for i in describe_info:
        data.append(i[:len(rank_list)])
    plt.rcParams['font.sans-serif'] = 'simhei'
    for i in range(len(target_list)):
        temp = data[i]
        plt.title(target_list[i], fontsize=18)
        a, p_text, l_text = plt.pie(temp, labels=temp, autopct='%1.1f%%')
        for t in p_text:
            t.set_size(18)
        for t in l_text:
            t.set_size(14)
        plt.legend(rank_list, loc=(1, 0.5), fontsize=11)
        plt.savefig('excel_output/img/pie/%s.png' % target_list[i])
        plt.clf()

    # 直方图绘制
    hist_data = []
    for i in range(len(rank_list)):
        hist_data.append([])
    for j in range(len(data[0])):
        for i in range(len(data)):
            hist_data[i].append(data[i][j])

    total_length = (len(rank_list) + 2) * len(target_list)
    step = len(rank_list) + 2
    x = list(range(0, total_length, step))
    for i in range(len(hist_data)):
        height = hist_data[i]
        plt.bar(height=height, x=x)
        for a, b in zip(x, height):
            plt.text(a, b, b, ha='center', va='bottom', fontsize=10)
        x = [i + 1 for i in x]
    plt.title('目标达成度人数分布柱状图')
    plt.xticks([i - step / 2 for i in x], rank_list)
    plt.legend(target_list, ncol=len(target_list), loc='center', bbox_to_anchor=(0.5, -0.1), frameon=False)
    plt.grid(axis='y', alpha=0.6)
    plt.savefig('excel_output/img/bar/分布柱状图.png')


def convertPicture(picture_path):
    img = Image.open(picture_path)
    save_path = picture_path.split('.')[0] + '.bmp'
    r, g, b, a = img.split()
    img = Image.merge("RGB", (r, g, b))
    img.save(save_path)
    return save_path


def creatXLS(describe_info, save_path, target_list, start_row=3):
    data_style = xlwt.XFStyle()
    data_style1 = xlwt.XFStyle()
    data_style2 = xlwt.XFStyle()
    title_style = xlwt.XFStyle()

    # 创建单元格对齐方式
    cellalign = xlwt.Alignment()
    cellalign.horz = 0x02
    cellalign.vert = 0x01
    cellalign1 = xlwt.Alignment()
    cellalign1.horz = 0x01
    cellalign1.vert = 0x00

    # 字体格式
    titlefont = xlwt.Font()
    titlefont.name = '宋体'
    titlefont.bold = True
    titlefont.height = 12 * 20

    # 创建单元格数据保留位数
    num_format_str = '#,##0.00'

    data_style1.num_format_str = num_format_str
    data_style.alignment = cellalign
    data_style1.alignment = cellalign
    data_style2.alignment = cellalign1
    title_style.font = titlefont
    title_style.alignment = cellalign

    # 之后可以不用看考虑
    # 创建新表
    wb = xlwt.Workbook(encoding='utf-8', style_compression=0)
    ws = wb.add_sheet('权重计算', cell_overwrite_ok=True)
    # 绘制表头
    ws.write_merge(0, 0, 0, 8, '2018-2019第二学期（002班） 钢结构设计原理 目标达成度分析表', title_style)
    ws.write_merge(1, 1, 0, 8, '课程：[CE30108]钢结构设计原理（建工、地下、安装方向）   上课班级：208211-002  任课教师：熊刚', data_style)
    rank_list = ['课程目标', '0-0.59', '0.60-0.69', '0.7-0.79', '0.8-0.89', '>0.9', '平均值', '最低值', '最高值']
    for i, item in enumerate(rank_list):
        ws.write(2, i, item, data_style)

    # 开始填入数据，之后若模板已经有了也可以删去
    for i in range(len(describe_info)):
        describe_info[i].insert(0, target_list[i])

    for i, item in enumerate(describe_info):
        for j, val in enumerate(item):
            if j <= 5:
                ws.write(i + start_row, j, val, data_style)
            else:
                ws.write(i + start_row, j, val, data_style1)

    picture_start_row = start_row + len(describe_info) + 1
    # 这里可能也不需要，0，8按表头的列数确定，如果删掉记得将picture_start_row-1
    ws.write_merge(picture_start_row - 1, picture_start_row - 1, 0, 8, '目标达成度人数分布', data_style)

    # 获取需要画的图片
    picture_list = []
    for i in target_list:
        picture_list.append('excel_output/img/pie/%s.png' % i)
    picture_list.append('excel_output/img/bar/分布柱状图.png')

    # pie每张图缩小为原来的0.4倍，8列一行的大概能放两张图,每张图宽大概占10行宽
    # bar图x缩小0.85，y缩小0.7，每张图宽大概占20行宽
    picture_area_lengths = math.ceil(5 / 2) * 11 + 20
    picture_end_row = picture_start_row + picture_area_lengths
    ws.write_merge(picture_start_row, picture_end_row, 0, 8)
    draw_row = picture_start_row

    for i in range(0, len(picture_list) - 2, 2):
        img1 = convertPicture(picture_list[i])
        img2 = convertPicture(picture_list[i + 1])
        ws.insert_bitmap(img1, draw_row, 0, x=20, y=20, scale_x=0.4, scale_y=0.4)
        ws.insert_bitmap(img2, draw_row, 4, x=50, y=20, scale_x=0.4, scale_y=0.4)
        # 加一个比图片宽略少的数
        draw_row += 11
    img3 = convertPicture(picture_list[-2])
    ws.insert_bitmap(img3, draw_row, 2, x=30, y=20, scale_x=0.4, scale_y=0.4)
    draw_row += 11
    img3 = convertPicture(picture_list[-1])
    ws.insert_bitmap(img3, draw_row, 0, x=12.5, y=20, scale_x=0.85, scale_y=0.7)

    ws.write_merge(picture_end_row + 1, picture_end_row + 8, 0, 8, '教学评价与改进措施：', data_style2)

    wb.save(save_path)


# if __name__ == '__main__':

#     pd_data, completion_list = get_data('excel_output/各班教学评价表.xls', sheel_name='各教学班评价表')

#     describe_info = getDescribeInfo(pd_data, completion_list=completion_list)

#     target_list = ['课程目标1', '课程目标2', '课程目标3', '课程目标4', '总目标']
#     rank_list = ['0-0.59', '0.60-0.69', '0.7-0.79', '0.8-0.89', '0.9-1']

#     drawDescibe(describe_info, target_list, rank_list)

#     # 输出的excel表位置，注意只能输出xls格式
#     save_path = 'excel_output/绘图表1.xls'
#     creatXLS(describe_info, save_path, target_list)
